package com.gxg.dao;

import com.gxg.entities.ExperimentalNode;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

@Repository
public class ExperimentalNodeDao
{
  @Autowired
  private JdbcTemplate jdbcTemplate;
  
  public List<ExperimentalNode> getFreeNode()
  {
    if (getFreeNodeCount() == 0) {
      return null;
    }
    String sql = "select * from ExperimentalNode where userId is null and time is null and status='正常'";
    
    List<ExperimentalNode> experimentalNodeList = this.jdbcTemplate.query(sql, new RowMapper()
    {
      public ExperimentalNode mapRow(ResultSet resultSet, int i)
        throws SQLException
      {
        ExperimentalNode experimentalNode = new ExperimentalNode();
        experimentalNode.setIp(resultSet.getString("ip"));
        
        return experimentalNode;
      }
    });
    return experimentalNodeList;
  }
  
  public int getFreeNodeCount()
  {
    String sql = "select count(*) from ExperimentalNode where userId is null and time is null and status='正常'";
    int rowCount = ((Integer)this.jdbcTemplate.queryForObject(sql, Integer.class)).intValue();
    return rowCount;
  }
  
  public void increaseNodeUser(String ip, String userId)
  {
    String sql = "update ExperimentalNode set userId=?, time=? where ip=?";
    Timestamp time = new Timestamp(System.currentTimeMillis());
    this.jdbcTemplate.update(sql, new Object[] { userId, time, ip });
  }
  
  public void updateNodeTime(String ip)
  {
    String sql = "update ExperimentalNode set time=? where ip=?";
    Timestamp time = new Timestamp(System.currentTimeMillis());
    this.jdbcTemplate.update(sql, new Object[] { time, ip });
  }
  
  public void updateNodeTimeByUserId(String userId)
  {
    String sql = "update ExperimentalNode set time=? where userId=?";
    Timestamp time = new Timestamp(System.currentTimeMillis());
    this.jdbcTemplate.update(sql, new Object[] { time, userId });
  }
  
  public int getNodeCountByUserId(String userId)
  {
    String sql = "select count(*) from ExperimentalNode where userId=? and status='正常'";
    int rowCount = ((Integer)this.jdbcTemplate.queryForObject(sql, Integer.class, new Object[] { userId })).intValue();
    return rowCount;
  }
  
  public List<ExperimentalNode> getNodeByUserId(String userId)
  {
    if (getNodeCountByUserId(userId) == 0) {
      return null;
    }
    String sql = "select * from ExperimentalNode where userId=? and status='正常'";
    List<ExperimentalNode> experimentalNodeList = this.jdbcTemplate.query(sql, new RowMapper()
    {
      public ExperimentalNode mapRow(ResultSet resultSet, int i)
        throws SQLException
      {
        ExperimentalNode experimentalNode = new ExperimentalNode();
        experimentalNode.setIp(resultSet.getString("ip"));
        experimentalNode.setUserId(resultSet.getString("userId"));
        experimentalNode.setDatetime(resultSet.getTimestamp("time"));
        experimentalNode.setStatus(resultSet.getString("status"));
        experimentalNode.setGroupNumber(resultSet.getInt("groupNumber"));
        return experimentalNode;
      }
    }, new Object[] { userId });
    
    return experimentalNodeList;
  }
  
  public void setUserIdAndTimeNullByIp(String ip)
  {
    String sql = "update ExperimentalNode set userId=null, time=null, status='正常' where ip=?";
    this.jdbcTemplate.update(sql, new Object[] { ip });
  }
  
  public int getNodeCountByUserIdAndTimeNotNull()
  {
    String sql = "select count(*) from ExperimentalNode where userId is not null and time is not null and status='正常'";
    int rowCount = ((Integer)this.jdbcTemplate.queryForObject(sql, Integer.class)).intValue();
    return rowCount;
  }
  
  public List<ExperimentalNode> getNodeByUserIdAndTimeNotNull()
  {
    if (getNodeCountByUserIdAndTimeNotNull() == 0) {
      return null;
    }
    String sql = "select * from ExperimentalNode where userId is not null and time is not null and status='正常'";
    List<ExperimentalNode> experimentalNodeList = this.jdbcTemplate.query(sql, new RowMapper()
    {
      public ExperimentalNode mapRow(ResultSet resultSet, int i)
        throws SQLException
      {
        ExperimentalNode experimentalNode = new ExperimentalNode();
        experimentalNode.setIp(resultSet.getString("ip"));
        experimentalNode.setUserId(resultSet.getString("userId"));
        experimentalNode.setDatetime(resultSet.getTimestamp("time"));
        experimentalNode.setStatus(resultSet.getString("status"));
        experimentalNode.setGroupNumber(resultSet.getInt("groupNumber"));
        return experimentalNode;
      }
    });
    return experimentalNodeList;
  }
  
  public int getNodeCountUserIdNullByIp(String ip)
  {
    String sql = "select count(*) from ExperimentalNode where ip=? and userId is null and status='正常'";
    int rowCount = ((Integer)this.jdbcTemplate.queryForObject(sql, Integer.class, new Object[] { ip })).intValue();
    return rowCount;
  }
  
  public int getNodeCountByIp(String ip)
  {
    String sql = "select count(*) from ExperimentalNode where ip=?";
    int rowCount = ((Integer)this.jdbcTemplate.queryForObject(sql, Integer.class, new Object[] { ip })).intValue();
    return rowCount;
  }
  
  public ExperimentalNode getNodeByIp(String ip)
  {
    String sql = "select * from ExperimentalNode where ip=?";
    ExperimentalNode experimentalNode = (ExperimentalNode)this.jdbcTemplate.queryForObject(sql, new RowMapper()
    {
      public ExperimentalNode mapRow(ResultSet resultSet, int i)
        throws SQLException
      {
        ExperimentalNode experimentalNode = new ExperimentalNode();
        experimentalNode.setIp(resultSet.getString("ip"));
        experimentalNode.setUserId(resultSet.getString("userId"));
        experimentalNode.setDatetime(resultSet.getTimestamp("time"));
        experimentalNode.setStatus(resultSet.getString("status"));
        experimentalNode.setGroupNumber(resultSet.getInt("groupNumber"));
        return experimentalNode;
      }
    }, new Object[] { ip });
    
    return experimentalNode;
  }
  
  public void setUserIdAndTimeNull()
  {
    String sql = "update ExperimentalNode set userId=null, time=null, status='正常'";
    this.jdbcTemplate.update(sql);
  }
  
  public int getNodeCount()
  {
    String sql = "select count(*) from ExperimentalNode";
    int rowCount = ((Integer)this.jdbcTemplate.queryForObject(sql, Integer.class)).intValue();
    return rowCount;
  }
  
  public List<ExperimentalNode> getAllNodeOrderByIp()
  {
    if (getNodeCount() == 0) {
      return null;
    }
    String sql = "select * from ExperimentalNode order by ip";
    List<ExperimentalNode> experimentalNodeList = this.jdbcTemplate.query(sql, new RowMapper()
    {
      public ExperimentalNode mapRow(ResultSet resultSet, int i)
        throws SQLException
      {
        ExperimentalNode experimentalNode = new ExperimentalNode();
        experimentalNode.setIp(resultSet.getString("ip"));
        experimentalNode.setUserId(resultSet.getString("userId"));
        experimentalNode.setDatetime(resultSet.getTimestamp("time"));
        experimentalNode.setStatus(resultSet.getString("status"));
        experimentalNode.setGroupNumber(resultSet.getInt("groupNumber"));
        return experimentalNode;
      }
    });
    return experimentalNodeList;
  }
  
  public void updateStatusByIp(String ip, String status)
  {
    String sql = "update ExperimentalNode set status=? where ip=?";
    this.jdbcTemplate.update(sql, new Object[] { status, ip });
  }
  
  public int getAvailableNodeCountByUserId(String userId)
  {
    String sql = "select count(*) from ExperimentalNode where (userId=? and status='正常') or (userId is null and time is null and status='正常')";
    int rowCount = ((Integer)this.jdbcTemplate.queryForObject(sql, Integer.class, new Object[] { userId })).intValue();
    return rowCount;
  }
  
  public List<ExperimentalNode> getAvailableNodeByUserIdOrderByIp(String userId)
  {
    if (getAvailableNodeCountByUserId(userId) == 0) {
      return null;
    }
    String sql = "select * from ExperimentalNode where (userId=? and status='正常') or (userId is null and time is null and status='正常') order by ip";
    List<ExperimentalNode> experimentalNodeList = this.jdbcTemplate.query(sql, new RowMapper()
    {
      public ExperimentalNode mapRow(ResultSet resultSet, int i)
        throws SQLException
      {
        ExperimentalNode experimentalNode = new ExperimentalNode();
        experimentalNode.setIp(resultSet.getString("ip"));
        experimentalNode.setUserId(resultSet.getString("userId"));
        experimentalNode.setDatetime(resultSet.getTimestamp("time"));
        experimentalNode.setStatus(resultSet.getString("status"));
        experimentalNode.setGroupNumber(resultSet.getInt("groupNumber"));
        return experimentalNode;
      }
    }, new Object[] { userId });
    
    return experimentalNodeList;
  }
  
  public void insertByIp(String ip)
  {
    String sql = "insert into ExperimentalNode values(?, null, null, '正常')";
    this.jdbcTemplate.update(sql, new Object[] { ip });
  }
  
  public void deleteAll()
  {
    String sql = "delete from ExperimentalNode";
    this.jdbcTemplate.update(sql);
  }
  
  public int getNodeCountAndStatusIsSuccess()
  {
    String sql = "select count(*) from ExperimentalNode where status='正常'";
    int rowCount = ((Integer)this.jdbcTemplate.queryForObject(sql, Integer.class)).intValue();
    return rowCount;
  }
  
  public List<ExperimentalNode> getAllStatusSuccessNode()
  {
    String sql = "select * from ExperimentalNode where status='正常'";
    List<ExperimentalNode> experimentalNodeList = this.jdbcTemplate.query(sql, new RowMapper()
    {
      public ExperimentalNode mapRow(ResultSet resultSet, int i)
        throws SQLException
      {
        ExperimentalNode experimentalNode = new ExperimentalNode();
        experimentalNode.setIp(resultSet.getString("ip"));
        experimentalNode.setUserId(resultSet.getString("userId"));
        experimentalNode.setDatetime(resultSet.getTimestamp("time"));
        experimentalNode.setStatus(resultSet.getString("status"));
        experimentalNode.setGroupNumber(resultSet.getInt("groupNumber"));
        return experimentalNode;
      }
    });
    return experimentalNodeList;
  }
  
  public int getCountByGroupNumber(int groupNumber)
  {
    String sql = "select count(*) from ExperimentalNode where groupNumber=?";
    int rowCount = ((Integer)this.jdbcTemplate.queryForObject(sql, Integer.class, new Object[] { Integer.valueOf(groupNumber) })).intValue();
    return rowCount;
  }
  
  public void insertByIpAndGroupNumber(String ip, int groupNumber)
  {
    String sql = "insert into ExperimentalNode values(?, null, null, '正常', ?)";
    this.jdbcTemplate.update(sql, new Object[] { ip, Integer.valueOf(groupNumber) });
  }
  
  public List<ExperimentalNode> getExperimentalNodeByGroupNumberAndStatusOrderByIp(int groupNumber, String status)
  {
    String sql = "select * from ExperimentalNode where groupNumber=? and status=?";
    List<ExperimentalNode> experimentalNodeList = this.jdbcTemplate.query(sql, new RowMapper()
    {
      public ExperimentalNode mapRow(ResultSet resultSet, int i)
        throws SQLException
      {
        ExperimentalNode experimentalNode = new ExperimentalNode();
        experimentalNode.setIp(resultSet.getString("ip"));
        experimentalNode.setUserId(resultSet.getString("userId"));
        experimentalNode.setDatetime(resultSet.getTimestamp("time"));
        experimentalNode.setStatus(resultSet.getString("status"));
        experimentalNode.setGroupNumber(resultSet.getInt("groupNumber"));
        return experimentalNode;
      }
    }, new Object[] {
    
      Integer.valueOf(groupNumber), status });
    return experimentalNodeList;
  }
  
  public int getCountByGroupNumberAndStatus(int groupNumber, String status)
  {
    String sql = "select count(*) from ExperimentalNode where groupNumber=? and status=?";
    int rowCount = ((Integer)this.jdbcTemplate.queryForObject(sql, Integer.class, new Object[] { Integer.valueOf(groupNumber), status })).intValue();
    return rowCount;
  }
  
  public List<ExperimentalNode> getExperimentalNodeByGroupNumber(int groupNumber)
  {
    String sql = "select * from ExperimentalNode where groupNumber=?";
    List<ExperimentalNode> experimentalNodeList = this.jdbcTemplate.query(sql, new RowMapper()
    {
      public ExperimentalNode mapRow(ResultSet resultSet, int i)
        throws SQLException
      {
        ExperimentalNode experimentalNode = new ExperimentalNode();
        experimentalNode.setIp(resultSet.getString("ip"));
        experimentalNode.setUserId(resultSet.getString("userId"));
        experimentalNode.setDatetime(resultSet.getTimestamp("time"));
        experimentalNode.setStatus(resultSet.getString("status"));
        experimentalNode.setGroupNumber(resultSet.getInt("groupNumber"));
        return experimentalNode;
      }
    }, new Object[] {
    
      Integer.valueOf(groupNumber) });
    return experimentalNodeList;
  }
  
  public void setAllGroupNumberNull()
  {
    String sql = "update ExperimentalNode set groupNumber=null";
    this.jdbcTemplate.update(sql);
  }
  
  public void setGroupNumberByIp(int groupNumber, String ip)
  {
    String sql = "update ExperimentalNode set groupNumber=? where ip=?";
    this.jdbcTemplate.update(sql, new Object[] { Integer.valueOf(groupNumber), ip });
  }
}
